package cn.iocoder.springboot.lab72;

import cn.iocoder.mssql.gentype.TestType;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcProcDemo {

    public static Connection getConnection(String username, String passwd) {
//        String driver = "org.postgresql.Driver";
//        String sourceURL = "jdbc:postgresql://192.168.56.21:5001/vastbase";

        String driver = "cn.com.vastbase.Driver";
        String sourceURL = "jdbc:vastbase://192.168.56.21:5001/vastbase";
        Connection conn;
        try {
            //加载数据库驱动。
            Class.forName(driver).newInstance();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        try {
            //创建数据库连接。
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("Connection succeed!");
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        return conn;
    }

    public void add() throws SQLException {
        Connection conn = getConnection("zxs", "Vastdata#0730");
        String sql = "INSERT INTO t1 (tt) VALUES (CAST(? AS typ_demo1))";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            // 假设你有一个 typ_demo1 类型的对象，这里我们使用字符串代替
            String compositeTypeValue = "some_value";
            stmt.setString(1, compositeTypeValue);
            int rowsInserted = stmt.executeUpdate();
            System.out.println(rowsInserted + " rows inserted");
        }


    }

    //       Array array = conn.createArrayOf("typ_collection_check_array", arrayData);
//        cs.setArray(1, array);
//        cs.setObject(1,st);
    //       PGobject pGobject = new PGobject();
//        pGobject.setType("typ_collection_check_array");
//        pGobject.setValue("{{a,b,c}}");

//        pGobject.setType("typ_collection_check_array");
//        pGobject.setValue("((a,b,c))}");

    //cs.setObject(1,"typ_collection_check_array('a','b','c'))", Types.OTHER);


   /* public static void main(String[] args) throws SQLException {
      test1();
      test2();
    }*/


    @Test
    public void test1() throws SQLException {
        Connection conn = getConnection("zxs", "Vastdata#0730");
        CallableStatement cs = conn.prepareCall("call p_ca_collection_check(?,?)");
        Object[][] arrayData = {{"a", "b", "c"}, {"a", "b", "c"}};
        Struct st = conn.createStruct("typ_collection_check_array", arrayData);
        cs.setObject(1, st, Types.STRUCT);
        cs.registerOutParameter(2, 1);
        boolean execute = cs.execute();
        int anInt = cs.getInt(2);
        System.out.println(anInt);
    }

    @Test
    public void test2() throws SQLException {
        Connection conn = getConnection("zxs", "Vastdata#0730");
        CallableStatement cs = conn.prepareCall("call p_ca_collection_check(?,?)");
//        Object[] arrayData = {"{a, b, c}"};
        Object[][] arrayData = {{"a", "b", "c"}, {"a", "b", "c"}};
        Array st = conn.createArrayOf("typ_collection_check_array", arrayData);
        cs.setArray(1, st);
        cs.registerOutParameter(2, 1);
        boolean execute = cs.execute();
        int anInt = cs.getInt(2);
        System.out.println(anInt);
    }

    //   Array st = conn.createArrayOf("test_type", new Object[]{1, 1});
    //cs.setArray(1, st);
//        cs.setInt(1, 1);
//        cs.setInt(2, 1);
//        cs.registerOutParameter(3, 11);

//        Struct st = conn.createStruct("test_type", new Object[]{1, 1});
//        PGobject obj = new PGobject();
//        obj.setType("test_type");
//        obj.setArrayValue(new String[]{"1","1"});
//        cs.setObject(1, obj, Types.OTHER);

//        PGobject obj = new PGobject();
//        obj.setType("test_type");
//        obj.setValue("{id:1,pid:1}");
//        ((PGConnection)conn).addDataType("test_type", TestType.class);

//        Map<String, Class<?>> typeMap = conn.getTypeMap();
//        if (typeMap == null) {
//            typeMap = new HashMap<>();
//        }
//        typeMap.put("provider", TestType.class);
//        conn.setTypeMap(typeMap);

    //        Object[] arrayData = {"1", "1"};
//        Struct st = conn.createStruct("test_type", arrayData);
//        TestType testType = new TestType();
//        testType.setId(1);
//        testType.setPid(2);
//        cs.setObject(1, testType, Types.OTHER);

    /**
     * //     * CREATE TYPE test_type AS TABLE ( ID INT, pid INT);
     * //     * <p>
     * //     * CREATE OR REPLACE PROCEDURE pro_2 ( pageindex test_type,  OUT total VARCHAR ) AS
     * //     * BEGIN
     * //     * total := '222';
     * //     * raise info '%',total;
     * //     * END;
     * <p>
     * create table t( ID INT, pid INT);
     * insert into t values(1,1);
     * insert into t values(2,2);
     * <p>
     * declare
     * a test_type;
     * b varchar:='';
     * begin
     * insert into a select * from t;
     * perform pro_2(a,b);
     * end;
     * /
     */
    @Test
    public void dd() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String driver = "org.postgresql.Driver";
        String url = "jdbc:postgresql://172.16.55.175:8888/zxsdb";
        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url, "zxs", "Vastdata#0730");
        CallableStatement cs = conn.prepareCall("call pro_2 (?,?)");

//        Object[][] arrayData = {{1, 1}};
//        Struct st = conn.createStruct("test_type", arrayData);
//        cs.setObject(1, st, Types.STRUCT);
//        cs.registerOutParameter(2, Types.VARCHAR);

        List<TestType> list = new ArrayList<>();
        list.add(new TestType(1, 1));
        list.add(new TestType(2, 2));
        cs.setObject(1, list, Types.STRUCT);
        cs.registerOutParameter(2, Types.VARCHAR);

        boolean execute = cs.execute();
        int total = cs.getInt(2);
        System.out.println(total);
    }

}
